package com.ruitaowang.goods.dao;

import com.ruitaowang.core.domain.Relations;
import com.ruitaowang.core.domain.RelationsVO;
import com.ruitaowang.core.domain.SysUser;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface RelationsAdMapper {

    List<Long> selectALLParents(Long userId);

    long countALLChilds(Relations relations);

    List<Long> selectALLChilds(Long userId);

    List<Relations> selectALLChildsForPage(Relations relations);

    List<SysUser> selectALLFriends(List<Long> userIds);

    List<Relations> selectALLChildsTwoForPage(Relations relations);

    @Select("select count(*) from rtshop.relations where pid in (select uesr_id from rtshop.relations where pid=#{pid})")

    Long spreadTwo(@Param("pid") Long pid);

//    @Select("SELECT s.id,s.headimgurl,s.nickname,r.count FROM (rtshop.t_sys_user s right join (SELECT pid,count(*) as count FROM rtshop.relations where ctime > #{stime} and ctime < #{etime} group by pid order by count desc limit 0,10) r on s.id = r.pid) left join (SELECT to_user_id,count(1) as count FROM rtshop.ls_msg where msg_type=4 group by to_user_id) m on s.id = m.to_user_id")
//    List<RelationsVO> selectPHB(RelationsVO relationsVO);

    @Select("SELECT s.id,s.headimgurl,s.nickname,r.count FROM (rtshop.t_sys_user s right join (SELECT pid,count(*) as count FROM rtshop.relations where ctime > #{stime} and ctime < #{etime} group by pid order by count desc limit 0,10) r on s.id = r.pid) order by r.count desc,s.remark desc limit 0,10")
    List<RelationsVO> selectPHB(RelationsVO relationsVO);

    @Select("SELECT count(*) FROM rtshop.relations where ctime > #{stime} and ctime < #{etime} and uesr_id = #{id}")
    long selectDZ(RelationsVO relationsVO);
    //微传媒历届中奖榜单
    @Select("select t.id,t.nickname,t.headimgurl,r.count from rtshop.t_sys_user t,(SELECT pid,count(*) as count FROM rtshop.relations where ctime > #{stime} and ctime < #{etime} group by pid order by count desc limit 0,3) r where r.pid = t.id")
    List<RelationsVO> selectRanking(RelationsVO relationsVO);

    //间推排行榜列表
//    @Select("select s.id,s.headimgurl,s.nickname,c.sumcount as count from (rtshop.t_sys_user s right join (SELECT a.pid,SUM(b.count) sumcount FROM (SELECT * FROM relations) a " +
//            "right join" +
//            "(SELECT pid,COUNT(1) COUNT FROM relations WHERE ctime > #{stime} and ctime < #{etime} GROUP BY pid) b on a.uesr_id = b.pid GROUP BY a.pid ORDER BY sumcount desc limit 0,10) c on s.id = c.pid);")
//    List<RelationsVO> selectTwoPHB(RelationsVO relationsVO);

    @Select("select s.id,s.headimgurl,s.nickname,c.sumcount as count from (rtshop.t_sys_user s right join (SELECT a.pid,SUM(b.count) sumcount FROM (SELECT * FROM relations ) AS a , " +
            "(SELECT pid,COUNT(1) COUNT FROM relations WHERE ctime > #{stime} and ctime < #{etime} " +
            "GROUP BY pid) AS b WHERE a.uesr_id=b.pid GROUP BY a.pid ORDER BY sumcount desc limit 0,10) c on s.id = c.pid);")
    List<RelationsVO> selectTwoPHB(RelationsVO relationsVO);

//    @Select("select s.id,s.headimgurl,s.nickname,c.sumcount as count from (rtshop.t_sys_user s right join (SELECT a.pid,SUM(b.count) sumcount FROM (SELECT * FROM relations) a " +
//            "inner join" +
//            "(SELECT pid,COUNT(1) COUNT FROM relations WHERE ctime > #{stime} and ctime < #{etime} GROUP BY pid) b on a.uesr_id = b.pid GROUP BY a.pid ORDER BY sumcount desc limit 0,10) c on s.id = c.pid);")
//    List<RelationsVO> selectTwoPHB(RelationsVO relationsVO);

    //间推个人推荐数量
    @Select("select count(1) from rtshop.relations where pid in(SELECT uesr_id FROM rtshop.relations where pid = #{id}) and ctime > #{stime} and ctime < #{etime}")
    long selectTowMe(RelationsVO relationsVO);

    //间推历届中奖榜单
//    @Select("select s.id,s.headimgurl,s.nickname,c.sumcount as count from (rtshop.t_sys_user s right join (SELECT a.pid,SUM(b.count) sumcount FROM (SELECT * FROM relations) a " +
//            "right join" +
//            "(SELECT pid,COUNT(1) COUNT FROM relations WHERE ctime > #{stime} and ctime < #{etime} GROUP BY pid) b on a.uesr_id = b.pid GROUP BY a.pid ORDER BY sumcount desc limit 0,3) c on s.id = c.pid);")
//    List<RelationsVO> selectTwoRanking(RelationsVO relationsVO);

    @Select("select s.id,s.headimgurl,s.nickname,c.sumcount as count from (rtshop.t_sys_user s right join (SELECT a.pid,SUM(b.count) sumcount FROM (SELECT * FROM relations ) AS a , " +
            "(SELECT pid,COUNT(1) COUNT FROM relations WHERE ctime > #{stime} and ctime < #{etime} " +
            "GROUP BY pid) AS b WHERE a.uesr_id=b.pid GROUP BY a.pid ORDER BY sumcount desc limit 0,3) c on s.id = c.pid);")
    List<RelationsVO> selectTwoRanking(RelationsVO relationsVO);
}